# -*-coding:utf-8-*-

import requests
from lxml import etree
import os
import time

# 国家统计局网址
base_url = 'http://www.stats.gov.cn/tjsj/tjbz/tjyqhdmhcxhfdm/2020/'


def getId(url):
    s = url.split("/")
    return s[len(s) - 1][0: len(s[len(s) - 1]) - 5]


def getProvinceTable():
    provinceTable_ = []
    response = requests.get(base_url)
    response.encoding = 'GBK'
    if response.status_code == 200:
        html = etree.HTML(response.text)
        provincetr = html.xpath('//tr[@class="provincetr"]/td/a')
        for tr in provincetr:
            name = tr.text
            url = tr.xpath('./@href')[0]
            id_ = getId(url)
            province = {'id': id_, 'name': name, 'url': url}
            f.write(
                '(%s,"%s", NULL,"%s",0,0),' % (id_, name, url) + os.linesep)
            provinceTable_.append(province)
    return provinceTable_


def getCityTable(url, classname):
    time.sleep(0.2)
    cityTable_ = []
    cityUrl = base_url + url
    print(classname, url)
    response = requests.get(cityUrl)
    response.encoding = 'GBK'
    # print(response.text)
    if response.status_code == 200:
        html = etree.HTML(response.text)
        citytr = html.xpath('//tr[@class="%s"]' % classname)
        for tr in citytr:
            tdlist = tr.findall('td')
            tdlen = len(tdlist)
            if tdlist and tdlen >= 2:
                city = {}
                i = 0

                for td in tdlist:
                    a = td.find('a')
                    if a is not None:
                        if i == 0:
                            city['code'] = a.text
                        else:
                            city['name'] = a.text
                            city['url'] = a.get('href')
                            city['id'] = getId(city['url'])
                            f.write(
                                '(%s,"%s","%s","%s",0,0),' % (city['id'], city['name'], city['code'], url) + os.linesep)
                    else:
                        if i == 0:
                            city['code'] = td.text
                        elif i == tdlen - 1:
                            if classname != 'villagetr':
                                city['name'] = td.text
                                print(td.text)
                                f.write('(%s,"%s","%s",NULL,0,0),' % (
                                    getId(url) + '01', city['name'], city['code']) + os.linesep)
                            else:
                                city['name'] = td.text
                                print(td.text)
                                f.write('(%s,"%s","%s",NULL,0,0),' % (
                                    city['code'], city['name'], city['code']) + os.linesep)

                    i += 1

                cityTable_.append(city)
            else:
                print(url, 'citytr has td is not 2')

    return cityTable_


if __name__ == '__main__':

    f = open(os.getcwd() + '/provice.sql', 'w')

    f.write('insert into province (id, name, code, url, level, p_id) values' + os.linesep)

    # ['provincetr', 'citytr', 'countytr', 'towntr', 'villagetr']

    # 11.html
    # http://www.stats.gov.cn/tjsj/tjbz/tjyqhdmhcxhfdm/2020/11.html

    # 11/1101.html
    # http://www.stats.gov.cn/tjsj/tjbz/tjyqhdmhcxhfdm/2020/11/1101.html

    # 01/110101.html
    # http://www.stats.gov.cn/tjsj/tjbz/tjyqhdmhcxhfdm/2020/11/01/110101.html

    # 01/110101001.html
    # http://www.stats.gov.cn/tjsj/tjbz/tjyqhdmhcxhfdm/2020/11/01/01/110101001.html

    provinceTable = getProvinceTable()
    for province in provinceTable:
        print(province['name'])
        if 'url' in province:
            cityTable = getCityTable(province['url'], 'citytr')
            for city in cityTable:
                print(city['name'])
                if 'url' in city:
                    u = city['url']
                    countyTable = getCityTable(u, 'countytr')
                    for county in countyTable:
                        print(county['name'])
                        if 'url' in county:
                            u = province['url'][0:2] + '/' + county['url']
                            townTable = getCityTable(u, 'towntr')
                            # for town in townTable:
                            #     print(town['name'])
                                # if 'url' in town:
                                #     u = city['url'][0:3] + county['url'][0:3] + town['url']
                                #     villageTable = getCityTable(u, 'villagetr')

    f.close()
